﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Data;

namespace QuanlyDanhsachThiCapPhong
{
    class HoiDongThi
    {
        string connstr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='C:\\Database\\Data.dat';Extended Properties=Excel 8.0";
        Function func = new Function();
        public void ThemHoiDongThi(string name)
        {
            String str = getHoiDongId();
            if (str == "" || str == null)
                str = "0";
            int id = Int32.Parse(str) + 1;
            OleDbConnection conn = new OleDbConnection(connstr);
            conn.Open();
            String query = "INSERT INTO [HoiDong$] VALUES('" + func.validId(id) + "', @hoidong, '','')";

            try
            {
                OleDbCommand cmd = new OleDbCommand(query, conn);
                cmd.Parameters.AddWithValue("@hoidong", SqlDbType.VarChar);
                cmd.Parameters["@hoidong"].Value = func.checkNull(name);
                cmd.ExecuteNonQuery();
                conn.Close();
            }
            catch (Exception e)
            {
               
            }
            conn.Close();
        }
        public void XoaHoiDongThi(string hid)
        {
            OleDbConnection conn = new OleDbConnection(connstr);
            conn.Open();
            String query = "UPDATE [HoiDong$] SET ID = '',[Hội đồng thi] = '' WHERE ID = '" + hid + "'";
            OleDbCommand cmd = new OleDbCommand(query, conn);
            cmd.ExecuteNonQuery();
            conn.Close();
        }
        public DataTable ListHoiDongThi()
        {
            OleDbConnection conn = new OleDbConnection(connstr);
            conn.Open();
            string query = "SELECT ID, [Hội đồng thi] FROM [HoiDong$] WHERE ID <> NULL";
            OleDbCommand cmd = new OleDbCommand(query, conn);
            OleDbDataAdapter da = new OleDbDataAdapter();
            da.SelectCommand = cmd;
            DataTable dt = new DataTable();
            da.Fill(dt);
            conn.Close();
            return dt;
        }
        public List<string> danhsachHoiDong ()
        {
            List<string> ds = new List<string>();
            OleDbConnection conn = new OleDbConnection(connstr);
            conn.Open();
            string query = "SELECT [Hội đồng thi] FROM [HoiDong$] WHERE ID <> NULL";
            OleDbCommand cmd = new OleDbCommand(query, conn);
            OleDbDataReader dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                ds.Add(dr.GetString(0));
            }
            conn.Close();
            return ds;
        }
        public void SapXep(string hoidong, string buoithi)
        {
       
        }
        public String getHoiDongId()
        {
            OleDbConnection conn = new OleDbConnection(connstr);
            conn.Open();
            string query = "SELECT MAX(ID) FROM [HoiDong$]";
            OleDbCommand cmd = new OleDbCommand(query, conn);
            try
            {
                string id = cmd.ExecuteScalar().ToString();
                conn.Close();
                return id;
            }
            catch (Exception ex)
            {
                conn.Close();
                return "0";
            }
        }
    }
}
